<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222" media="(prefers-color-scheme: light)">
<meta name="theme-color" content="#222" media="(prefers-color-scheme: dark)"><meta name="generator" content="Hexo 6.3.0">
<link rel="preconnect" href="https://fonts.googleapis.com" crossorigin>
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Source+Sans+Pro:300,300italic,400,400italic,700,700italic%7CZCOOL+QingKe+HuangYou:300,300italic,400,400italic,700,700italic%7CNoto+Sans+Simplified+Chinese:300,300italic,400,400italic,700,700italic%7CJetBrains+Mono:300,300italic,400,400italic,700,700italic&display=swap&subset=latin,latin-ext">

<link rel="stylesheet" href="/lib/@fortawesome/fontawesome-free/css/all.min.css" integrity="sha256-HtsXJanqjKTc8vVQjO4YMhiqFoXkfBsjBWcX91T1jr8=" crossorigin="anonymous">
  <link rel="stylesheet" href="/lib/animate.css/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">
  <link rel="stylesheet" href="/lib/@fancyapps/ui/dist/fancybox/fancybox.css" integrity="sha256-RvRHGSuWAxZpXKV9lLDt2e+rZ+btzn48Wp4ueS3NZKs=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"wyatex.online","root":"/","images":"/images","scheme":"Pisces","darkmode":true,"version":"8.18.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":{"enable":true,"style":"mac"},"fold":{"enable":false,"height":500},"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":true,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":true,"transition":{"menu_item":"fadeInDown","post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"},"path":"/search.xml","localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":true}}</script><script src="/js/config.js"></script>

    <meta name="description" content="本文针对关系型数据库的一般语法。限于篇幅，本文侧重说明用法，不会展开讲解特性、原理。">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL 语法速成手册">
<meta property="og:url" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/index.html">
<meta property="og:site_name" content="Wyatex&#96;s blog">
<meta property="og:description" content="本文针对关系型数据库的一般语法。限于篇幅，本文侧重说明用法，不会展开讲解特性、原理。">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/1.webp">
<meta property="og:image" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/2.webp">
<meta property="og:image" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/3.webp">
<meta property="og:image" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/4.webp">
<meta property="og:image" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/5.webp">
<meta property="og:image" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/6.webp">
<meta property="og:image" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/7.webp">
<meta property="article:published_time" content="2020-11-20T11:01:59.000Z">
<meta property="article:modified_time" content="2023-05-05T01:25:47.068Z">
<meta property="article:author" content="Wyatex">
<meta property="article:tag" content="学习笔记">
<meta property="article:tag" content="数据库">
<meta property="article:tag" content="SQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/1.webp">


<link rel="canonical" href="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/","path":"学习笔记/SQL-语法速成手册/","title":"SQL 语法速成手册"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>SQL 语法速成手册 | Wyatex`s blog</title>
  








  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <div class="column">
      <header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <p class="site-title">Wyatex`s blog</p>
      <i class="logo-line"></i>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger" aria-label="搜索" role="button">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-about"><a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签<span class="badge">97</span></a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类<span class="badge">26</span></a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档<span class="badge">155</span></a></li><li class="menu-item menu-item-编程"><a href="/programming/" rel="section"><i class="fa fa-book fa-fw"></i>编程</a></li><li class="menu-item menu-item-蹲坑读物"><a href="/%E5%B9%B2%E8%B4%A7/%E5%BC%80%E5%8F%91%E4%BA%A4%E6%B5%81%E7%BE%A4%E5%88%86%E4%BA%AB%E8%AE%B0%E5%BD%95/" rel="section"><i class="fa fa-book fa-fw"></i>蹲坑读物</a></li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup"><div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off" maxlength="80"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close" role="button">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div class="search-result-container no-result">
  <div class="search-result-icon">
    <i class="fa fa-spinner fa-pulse fa-5x"></i>
  </div>
</div>

    </div>
  </div>

</header>
        
  
  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5"><span class="nav-number">1.</span> <span class="nav-text">基本概念</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%AF%E8%AF%AD"><span class="nav-number">1.1.</span> <span class="nav-text">数据库术语</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#SQL-%E8%AF%AD%E6%B3%95"><span class="nav-number">1.2.</span> <span class="nav-text">SQL 语法</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#SQL-%E8%AF%AD%E6%B3%95%E7%BB%93%E6%9E%84"><span class="nav-number">1.2.1.</span> <span class="nav-text">SQL 语法结构</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#SQL-%E8%AF%AD%E6%B3%95%E8%A6%81%E7%82%B9"><span class="nav-number">1.2.2.</span> <span class="nav-text">SQL 语法要点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#SQL%E8%AF%AD%E8%A8%80%E7%9A%84%E5%88%86%E7%B1%BB"><span class="nav-number">1.2.3.</span> <span class="nav-text">SQL语言的分类</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%AE%9A%E4%B9%89%E8%AF%AD%E8%A8%80%EF%BC%88DDL%EF%BC%89"><span class="nav-number">1.2.3.1.</span> <span class="nav-text">数据定义语言（DDL）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E6%93%8D%E7%BA%B5%E8%AF%AD%E8%A8%80%EF%BC%88DML%EF%BC%89"><span class="nav-number">1.2.3.2.</span> <span class="nav-text">数据操纵语言（DML）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E4%BA%8B%E5%8A%A1%E6%8E%A7%E5%88%B6%E8%AF%AD%E8%A8%80%EF%BC%88TCL%EF%BC%89"><span class="nav-number">1.2.3.3.</span> <span class="nav-text">事务控制语言（TCL）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E6%8E%A7%E5%88%B6%E8%AF%AD%E8%A8%80%EF%BC%88DCL%EF%BC%89"><span class="nav-number">1.2.3.4.</span> <span class="nav-text">数据控制语言（DCL）</span></a></li></ol></li></ol></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%A2%9E%E5%88%A0%E6%94%B9%E6%9F%A5"><span class="nav-number">2.</span> <span class="nav-text">增删改查</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE"><span class="nav-number">2.1.</span> <span class="nav-text">插入数据</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9B%B4%E6%96%B0%E6%95%B0%E6%8D%AE"><span class="nav-number">2.2.</span> <span class="nav-text">更新数据</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE"><span class="nav-number">2.3.</span> <span class="nav-text">删除数据</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE"><span class="nav-number">2.4.</span> <span class="nav-text">查询数据</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="nav-number">3.</span> <span class="nav-text">子查询</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#WHERE"><span class="nav-number">3.1.</span> <span class="nav-text">WHERE</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#IN-%E5%92%8C-BETWEEN"><span class="nav-number">3.2.</span> <span class="nav-text">IN 和 BETWEEN</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#AND%E3%80%81OR%E3%80%81NOT"><span class="nav-number">3.3.</span> <span class="nav-text">AND、OR、NOT</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#LIKE"><span class="nav-number">3.4.</span> <span class="nav-text">LIKE</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#CASE-WHEN%E8%AF%AD%E5%8F%A5"><span class="nav-number">3.5.</span> <span class="nav-text">CASE WHEN语句</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E8%BF%9E%E6%8E%A5%E5%92%8C%E7%BB%84%E5%90%88"><span class="nav-number">4.</span> <span class="nav-text">连接和组合</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%BF%9E%E6%8E%A5%EF%BC%88JOIN%EF%BC%89"><span class="nav-number">4.1.</span> <span class="nav-text">连接（JOIN）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%BB%84%E5%90%88%EF%BC%88UNION%EF%BC%89"><span class="nav-number">4.2.</span> <span class="nav-text">组合（UNION）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#JOIN-vs-UNION"><span class="nav-number">4.3.</span> <span class="nav-text">JOIN vs UNION</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%87%BD%E6%95%B0"><span class="nav-number">5.</span> <span class="nav-text">函数</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%96%87%E6%9C%AC%E5%A4%84%E7%90%86"><span class="nav-number">5.1.</span> <span class="nav-text">文本处理</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%97%A5%E6%9C%9F%E5%92%8C%E6%97%B6%E9%97%B4%E5%A4%84%E7%90%86"><span class="nav-number">5.2.</span> <span class="nav-text">日期和时间处理</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E5%80%BC%E5%A4%84%E7%90%86"><span class="nav-number">5.3.</span> <span class="nav-text">数值处理</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%B1%87%E6%80%BB"><span class="nav-number">5.4.</span> <span class="nav-text">汇总</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%8E%92%E5%BA%8F%E5%92%8C%E5%88%86%E7%BB%84"><span class="nav-number">6.</span> <span class="nav-text">排序和分组</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#ORDER-BY"><span class="nav-number">6.1.</span> <span class="nav-text">ORDER BY</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#GROUP-BY"><span class="nav-number">6.2.</span> <span class="nav-text">GROUP BY</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#HAVING"><span class="nav-number">6.3.</span> <span class="nav-text">HAVING</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%AE%9A%E4%B9%89"><span class="nav-number">7.</span> <span class="nav-text">数据定义</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%EF%BC%88DATABASE%EF%BC%89"><span class="nav-number">7.1.</span> <span class="nav-text">数据库（DATABASE）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E8%A1%A8%EF%BC%88TABLE%EF%BC%89"><span class="nav-number">7.2.</span> <span class="nav-text">数据表（TABLE）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E8%A1%A8%EF%BC%88TABLE%EF%BC%89-1"><span class="nav-number">7.3.</span> <span class="nav-text">数据表（TABLE）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%A7%86%E5%9B%BE%EF%BC%88VIEW%EF%BC%89"><span class="nav-number">7.4.</span> <span class="nav-text">视图（VIEW）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%B4%A2%E5%BC%95%EF%BC%88INDEX%EF%BC%89"><span class="nav-number">7.5.</span> <span class="nav-text">索引（INDEX）</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E7%BA%A6%E6%9D%9F"><span class="nav-number">7.6.</span> <span class="nav-text">约束</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86"><span class="nav-number">8.</span> <span class="nav-text">事务处理</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%9D%83%E9%99%90%E6%8E%A7%E5%88%B6"><span class="nav-number">9.</span> <span class="nav-text">权限控制</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B"><span class="nav-number">10.</span> <span class="nav-text">存储过程</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%B8%B8%E6%A0%87"><span class="nav-number">11.</span> <span class="nav-text">游标</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="nav-number">12.</span> <span class="nav-text">触发器</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#BEGIN-%E5%92%8C-END"><span class="nav-number">12.1.</span> <span class="nav-text">BEGIN 和 END</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#NEW-%E5%92%8C-OLD"><span class="nav-number">12.2.</span> <span class="nav-text">NEW 和 OLD</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%88%9B%E5%BB%BA%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="nav-number">12.3.</span> <span class="nav-text">创建触发器</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9F%A5%E7%9C%8B%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="nav-number">12.4.</span> <span class="nav-text">查看触发器</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%88%A0%E9%99%A4%E8%A7%A6%E5%8F%91%E5%99%A8"><span class="nav-number">12.5.</span> <span class="nav-text">删除触发器</span></a></li></ol></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="Wyatex"
      src="/images/avatar.jpg">
  <p class="site-author-name" itemprop="name">Wyatex</p>
  <div class="site-description" itemprop="description">这里是我用来做笔记的网站哟~</div>
</div>
<div class="site-state-wrap animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives/">
          <span class="site-state-item-count">155</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories/">
        <span class="site-state-item-count">26</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags/">
        <span class="site-state-item-count">97</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author animated">
      <span class="links-of-author-item">
        <a href="https://github.com/wyatex" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;wyatex" rel="noopener me" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="mailto:wyatex@qq.com" title="E-Mail → mailto:wyatex@qq.com" rel="noopener me" target="_blank"><i class="fa fa-envelope fa-fw"></i>E-Mail</a>
      </span>
  </div>

        </div>
      </div>
    </div>

    
    <div class="sidebar-inner sidebar-blogroll">
      <div class="links-of-blogroll animated">
        <div class="links-of-blogroll-title"><i class="fa fa-globe fa-fw"></i>
          链接
        </div>
        <ul class="links-of-blogroll-list">
            <li class="links-of-blogroll-item">
              <a href="https://antfu.me/" title="https:&#x2F;&#x2F;antfu.me&#x2F;" rel="noopener" target="_blank">Anthony Fu</a>
            </li>
            <li class="links-of-blogroll-item">
              <a href="https://www.codesky.me/" title="https:&#x2F;&#x2F;www.codesky.me&#x2F;" rel="noopener" target="_blank">CodeSky</a>
            </li>
            <li class="links-of-blogroll-item">
              <a href="https://learn.lianglianglee.com/" title="https:&#x2F;&#x2F;learn.lianglianglee.com&#x2F;" rel="noopener" target="_blank">技术文章摘抄</a>
            </li>
        </ul>
      </div>
    </div>
        <div class="pjax">
        <div class="sidebar-inner sidebar-post-related">
          <div class="animated">
              <div class="links-of-blogroll-title"><i class="fa fa-signs-post fa-fw"></i>
    相关文章
  </div>
  <ul class="popular-posts">
    <li class="popular-posts-item">
      <a class="popular-posts-link" href="/%E7%AC%94%E8%AE%B0/%E4%BD%BF%E7%94%A8docker%E9%83%A8%E7%BD%B2mysql/" rel="bookmark">
        <time class="popular-posts-time">2020-05-06</time>
        <br>
      使用docker部署mysql
      </a>
    </li>
    <li class="popular-posts-item">
      <a class="popular-posts-link" href="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E4%BA%8B%E5%8A%A1%E5%A4%84%E7%90%86/" rel="bookmark">
        <time class="popular-posts-time">2020-05-21</time>
        <br>
      什么是事务处理，如何使用COMMIT和ROLLBACK进行操作？
      </a>
    </li>
    <li class="popular-posts-item">
      <a class="popular-posts-link" href="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E7%AC%94%E8%AE%B0/" rel="bookmark">
        <time class="popular-posts-time">2020-05-20</time>
        <br>
      存储过程笔记
      </a>
    </li>
  </ul>

          </div>
        </div>
        </div>
  </aside>


    </div>

    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.jpg">
      <meta itemprop="name" content="Wyatex">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Wyatex`s blog">
      <meta itemprop="description" content="这里是我用来做笔记的网站哟~">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="SQL 语法速成手册 | Wyatex`s blog">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          SQL 语法速成手册
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2020-11-20 19:01:59" itemprop="dateCreated datePublished" datetime="2020-11-20T19:01:59+08:00">2020-11-20</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-05-05 09:25:47" itemprop="dateModified" datetime="2023-05-05T09:25:47+08:00">2023-05-05</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" itemprop="url" rel="index"><span itemprop="name">学习笔记</span></a>
        </span>
    </span>

  
    <span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv">
      <span class="post-meta-item-icon">
        <i class="far fa-eye"></i>
      </span>
      <span class="post-meta-item-text">阅读次数：</span>
      <span id="busuanzi_value_page_pv"></span>
    </span>
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody"><p>本文针对关系型数据库的一般语法。限于篇幅，本文侧重说明用法，不会展开讲解特性、原理。</p>
<span id="more"></span>

<h1 id="基本概念"><a href="#基本概念" class="headerlink" title="基本概念"></a>基本概念</h1><h2 id="数据库术语"><a href="#数据库术语" class="headerlink" title="数据库术语"></a>数据库术语</h2><p><strong>数据库（database）</strong> - 保存有组织的数据的容器（通常是一个文件或一组文件）。</p>
<p><strong>数据表（table）</strong> - 某种特定类型数据的结构化清单。</p>
<p><strong>模式（schema）</strong> - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。</p>
<p><strong>列（column）</strong> - 表中的一个字段。所有表都是由一个或多个列组成的。</p>
<p><strong>行（row）</strong> - 表中的一个记录。</p>
<p><strong>主键（primary key）</strong> - 一列（或一组列），其值能够唯一标识表中每一行。</p>
<h2 id="SQL-语法"><a href="#SQL-语法" class="headerlink" title="SQL 语法"></a>SQL 语法</h2><p>SQL（Structured Query Language)，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL&#x2F;SQL、Transact-SQL 等。</p>
<h3 id="SQL-语法结构"><a href="#SQL-语法结构" class="headerlink" title="SQL 语法结构"></a>SQL 语法结构</h3><p>SQL 语法结构包括：</p>
<ul>
<li><strong>子句</strong> - 是语句和查询的组成成分。（在某些情况下，这些都是可选的。）</li>
<li><strong>表达式</strong> - 可以产生任何标量值，或由列和行的数据库表</li>
<li><strong>谓词</strong> - 给需要评估的 SQL 三值逻辑（3VL）（true&#x2F;false&#x2F;unknown）或布尔真值指定条件，并限制语句和查询的效果，或改变程序流程。</li>
<li><strong>查询</strong> - 基于特定条件检索数据。这是 SQL 的一个重要组成部分。</li>
<li><strong>语句</strong> - 可以持久地影响纲要和数据，也可以控制数据库事务、程序流程、连接、会话或诊断。</li>
</ul>
<h3 id="SQL-语法要点"><a href="#SQL-语法要点" class="headerlink" title="SQL 语法要点"></a>SQL 语法要点</h3><p>SQL 语句不区分大小写，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。</p>
<p>例如：SELECT 与 select 、Select 是相同的。</p>
<p>多条 SQL 语句必须以分号（;）分隔。</p>
<p>处理 SQL 语句时，所有空格都被忽略。SQL 语句可以写成一行，也可以分写为多行。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 一行 SQL 语句</span></span><br><span class="line"><span class="keyword">UPDATE</span> <span class="keyword">user</span> <span class="keyword">SET</span> username<span class="operator">=</span><span class="string">&#x27;robot&#x27;</span>, password<span class="operator">=</span><span class="string">&#x27;robot&#x27;</span> <span class="keyword">WHERE</span> username <span class="operator">=</span> <span class="string">&#x27;root&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 多行 SQL 语句</span></span><br><span class="line"><span class="keyword">UPDATE</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">SET</span> username<span class="operator">=</span><span class="string">&#x27;robot&#x27;</span>, password<span class="operator">=</span><span class="string">&#x27;robot&#x27;</span></span><br><span class="line"><span class="keyword">WHERE</span> username <span class="operator">=</span> <span class="string">&#x27;root&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p>SQL 支持三种注释</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">## 注释<span class="number">1</span></span><br><span class="line"><span class="comment">-- 注释2</span></span><br><span class="line"><span class="comment">/* 注释3 */</span></span><br></pre></td></tr></table></figure>

<h3 id="SQL语言的分类"><a href="#SQL语言的分类" class="headerlink" title="SQL语言的分类"></a>SQL语言的分类</h3><h4 id="数据定义语言（DDL）"><a href="#数据定义语言（DDL）" class="headerlink" title="数据定义语言（DDL）"></a>数据定义语言（DDL）</h4><p>数据定义语言（Data Definition Language，DDL）是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。</p>
<p>DDL 的主要功能是定义数据库对象。</p>
<p>DDL 的核心指令是 CREATE、ALTER、DROP。</p>
<h4 id="数据操纵语言（DML）"><a href="#数据操纵语言（DML）" class="headerlink" title="数据操纵语言（DML）"></a>数据操纵语言（DML）</h4><p>数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句。</p>
<p>DML 的主要功能是 访问数据，因此其语法都是以读写数据库为主。</p>
<p>DML 的核心指令是 INSERT、UPDATE、DELETE、SELECT。这四个指令合称 CRUD(Create, Read, Update, Delete)，即增删改查。</p>
<h4 id="事务控制语言（TCL）"><a href="#事务控制语言（TCL）" class="headerlink" title="事务控制语言（TCL）"></a>事务控制语言（TCL）</h4><p>事务控制语言 (Transaction Control Language, TCL) 用于管理数据库中的事务。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。</p>
<p>TCL 的核心指令是 COMMIT、ROLLBACK。</p>
<h4 id="数据控制语言（DCL）"><a href="#数据控制语言（DCL）" class="headerlink" title="数据控制语言（DCL）"></a>数据控制语言（DCL）</h4><p>数据控制语言 (Data Control Language, DCL) 是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。</p>
<p>DCL 的核心指令是 GRANT、REVOKE。</p>
<p>DCL 以控制用户的访问权限为主，因此其指令作法并不复杂，可利用 DCL 控制的权限有：CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。</p>
<blockquote>
<p>根据不同的 DBMS 以及不同的安全性实体，其支持的权限控制也有所不同。</p>
</blockquote>
<h1 id="增删改查"><a href="#增删改查" class="headerlink" title="增删改查"></a>增删改查</h1><p>增删改查，又称为 CRUD，数据库基本操作中的基本操作。</p>
<h2 id="插入数据"><a href="#插入数据" class="headerlink" title="插入数据"></a>插入数据</h2><p>INSERT INTO 语句用于向表中插入新记录。</p>
<p>插入完整的行</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">VALUES</span> (<span class="number">10</span>, <span class="string">&#x27;root&#x27;</span>, <span class="string">&#x27;root&#x27;</span>, <span class="string">&#x27;xxxx@163.com&#x27;</span>);</span><br></pre></td></tr></table></figure>

<p>插入行的一部分</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="keyword">user</span>(username, password, email)</span><br><span class="line"><span class="keyword">VALUES</span> (<span class="string">&#x27;admin&#x27;</span>, <span class="string">&#x27;admin&#x27;</span>, <span class="string">&#x27;xxxx@163.com&#x27;</span>);</span><br></pre></td></tr></table></figure>

<p>插入查询出来的数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="keyword">user</span>(username)</span><br><span class="line"><span class="keyword">SELECT</span> name</span><br><span class="line"><span class="keyword">FROM</span> account;</span><br></pre></td></tr></table></figure>

<h2 id="更新数据"><a href="#更新数据" class="headerlink" title="更新数据"></a>更新数据</h2><p>UPDATE 语句用于更新表中的记录。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">SET</span> username<span class="operator">=</span><span class="string">&#x27;robot&#x27;</span>, password<span class="operator">=</span><span class="string">&#x27;robot&#x27;</span></span><br><span class="line"><span class="keyword">WHERE</span> username <span class="operator">=</span> <span class="string">&#x27;root&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h2 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h2><ul>
<li>DELETE 语句用于删除表中的记录。</li>
<li>TRUNCATE TABLE 可以清空表，也就是删除所有行。</li>
</ul>
<p>删除表中的指定数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">WHERE</span> username <span class="operator">=</span> <span class="string">&#x27;robot&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>清空表中的数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">TRUNCATE</span> <span class="keyword">TABLE</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>

<h2 id="查询数据"><a href="#查询数据" class="headerlink" title="查询数据"></a>查询数据</h2><ul>
<li>SELECT 语句用于从数据库中查询数据。</li>
<li>DISTINCT 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。</li>
<li>LIMIT 限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。<ul>
<li>ASC ：升序（默认）</li>
<li>DESC ：降序</li>
</ul>
</li>
</ul>
<p>查询单列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_name</span><br><span class="line"><span class="keyword">FROM</span> products;</span><br></pre></td></tr></table></figure>

<p>查询多列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_name, prod_price</span><br><span class="line"><span class="keyword">FROM</span> products;</span><br></pre></td></tr></table></figure>

<p>查询所有列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ELECT <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> products;</span><br></pre></td></tr></table></figure>

<p>查询不同的值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span></span><br><span class="line">vend_id <span class="keyword">FROM</span> products;</span><br></pre></td></tr></table></figure>

<p>限制查询结果</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 返回前 5 行</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> mytable LIMIT <span class="number">5</span>;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> mytable LIMIT <span class="number">0</span>, <span class="number">5</span>;</span><br><span class="line"><span class="comment">-- 返回第 3 ~ 5 行</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> mytable LIMIT <span class="number">2</span>, <span class="number">3</span>;</span><br></pre></td></tr></table></figure>

<h1 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h1><p>子查询是嵌套在较大查询中的 SQL 查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。</p>
<ul>
<li>子查询可以嵌套在 SELECT，INSERT，UPDATE 或 DELETE 语句内或另一个子查询中。</li>
<li>子查询通常会在另一个 SELECT 语句的 WHERE 子句中添加。</li>
<li>您可以使用比较运算符，如 &gt;，&lt;，或 &#x3D;。比较运算符也可以是多行运算符，如 IN，ANY 或 ALL。</li>
<li>子查询必须被圆括号 () 括起来。</li>
<li>内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。执行过程可以参考下图：<img data-src="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/1.webp" class=""></li>
</ul>
<p>子查询的子查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> cust_name, cust_contact</span><br><span class="line"><span class="keyword">FROM</span> customers</span><br><span class="line"><span class="keyword">WHERE</span> cust_id <span class="keyword">IN</span> (<span class="keyword">SELECT</span> cust_id</span><br><span class="line">                    <span class="keyword">FROM</span> orders</span><br><span class="line">                    <span class="keyword">WHERE</span> order_num <span class="keyword">IN</span> (<span class="keyword">SELECT</span> order_num</span><br><span class="line">                                        <span class="keyword">FROM</span> orderitems</span><br><span class="line">                                        <span class="keyword">WHERE</span> prod_id <span class="operator">=</span> <span class="string">&#x27;RGAN01&#x27;</span>));</span><br></pre></td></tr></table></figure>

<h2 id="WHERE"><a href="#WHERE" class="headerlink" title="WHERE"></a>WHERE</h2><ul>
<li>WHERE 子句用于过滤记录，即缩小访问数据的范围。</li>
<li>WHERE 后跟一个返回 true 或 false 的条件。</li>
<li>WHERE 可以与 SELECT，UPDATE 和 DELETE 一起使用。</li>
<li>可以在 WHERE 子句中使用的操作符<img data-src="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/2.webp" class=""></li>
</ul>
<p>SELECT 语句中的 WHERE 子句</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Customers</span><br><span class="line"><span class="keyword">WHERE</span> cust_name <span class="operator">=</span> <span class="string">&#x27;Kids Place&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>UPDATE 语句中的 WHERE 子句</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> Customers</span><br><span class="line"><span class="keyword">SET</span> cust_name <span class="operator">=</span> <span class="string">&#x27;Jack Jones&#x27;</span></span><br><span class="line"><span class="keyword">WHERE</span> cust_name <span class="operator">=</span> <span class="string">&#x27;Kids Place&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>DELETE 语句中的 WHERE 子句</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> Customers</span><br><span class="line"><span class="keyword">WHERE</span> cust_name <span class="operator">=</span> <span class="string">&#x27;Kids Place&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h2 id="IN-和-BETWEEN"><a href="#IN-和-BETWEEN" class="headerlink" title="IN 和 BETWEEN"></a>IN 和 BETWEEN</h2><ul>
<li>IN 操作符在 WHERE 子句中使用，作用是在指定的几个特定值中任选一个值。</li>
<li>BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于某个范围内的值。</li>
</ul>
<p>IN 示例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> vend_id <span class="keyword">IN</span> (<span class="string">&#x27;DLL01&#x27;</span>, <span class="string">&#x27;BRS01&#x27;</span>);</span><br></pre></td></tr></table></figure>

<p>BETWEEN 示例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_price <span class="keyword">BETWEEN</span> <span class="number">3</span> <span class="keyword">AND</span> <span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<h2 id="AND、OR、NOT"><a href="#AND、OR、NOT" class="headerlink" title="AND、OR、NOT"></a>AND、OR、NOT</h2><ul>
<li>AND、OR、NOT 是用于对过滤条件的逻辑处理指令。</li>
<li>AND 优先级高于 OR，为了明确处理顺序，可以使用 ()。</li>
<li>AND 操作符表示左右条件都要满足。</li>
<li>OR 操作符表示左右条件满足任意一个即可。</li>
<li>NOT 操作符用于否定一个条件。</li>
</ul>
<p>AND 示例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_name, prod_price</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> vend_id <span class="operator">=</span> <span class="string">&#x27;DLL01&#x27;</span> <span class="keyword">AND</span> prod_price <span class="operator">&lt;=</span> <span class="number">4</span>;</span><br></pre></td></tr></table></figure>

<p>OR 示例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_name, prod_price</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> vend_id <span class="operator">=</span> <span class="string">&#x27;DLL01&#x27;</span> <span class="keyword">OR</span> vend_id <span class="operator">=</span> <span class="string">&#x27;BRS01&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>NOT 示例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_price <span class="keyword">NOT</span> <span class="keyword">BETWEEN</span> <span class="number">3</span> <span class="keyword">AND</span> <span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<h2 id="LIKE"><a href="#LIKE" class="headerlink" title="LIKE"></a>LIKE</h2><ul>
<li>LIKE 操作符在 WHERE 子句中使用，作用是确定字符串是否匹配模式。</li>
<li>只有字段是文本值时才使用 LIKE。</li>
<li>LIKE 支持两个通配符匹配选项：% 和 _。</li>
<li>不要滥用通配符，通配符位于开头处匹配会非常慢。</li>
<li>% 表示任何字符出现任意次数。</li>
<li>_ 表示任何字符出现一次。</li>
</ul>
<p>% 示例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_name, prod_price</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_name <span class="keyword">LIKE</span> <span class="string">&#x27;%bean bag%&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>_ 示例</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> prod_id, prod_name, prod_price</span><br><span class="line"><span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">WHERE</span> prod_name <span class="keyword">LIKE</span> <span class="string">&#x27;__ inch teddy bear&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h2 id="CASE-WHEN语句"><a href="#CASE-WHEN语句" class="headerlink" title="CASE WHEN语句"></a>CASE WHEN语句</h2><p>case的第一个作用是判断的同时改变其值</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> username,</span><br><span class="line"><span class="keyword">case</span> <span class="keyword">when</span> <span class="string">&#x27;admin&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;管理员&#x27;</span></span><br><span class="line">     <span class="keyword">when</span> <span class="string">&#x27;user&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;普通用户&#x27;</span></span><br><span class="line">     <span class="keyword">else</span> <span class="string">&#x27;其他用户&#x27;</span> <span class="keyword">end</span> role</span><br><span class="line"><span class="keyword">from</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>
<p>也可以这样：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span>  id, </span><br><span class="line">        <span class="keyword">CASE</span> <span class="keyword">WHEN</span> sex <span class="operator">=</span> <span class="string">&#x27;0&#x27;</span> <span class="keyword">THEN</span> <span class="string">&#x27;男&#x27;</span></span><br><span class="line">             <span class="keyword">WHEN</span> sex <span class="operator">=</span> <span class="string">&#x27;1&#x27;</span> <span class="keyword">THEN</span> <span class="string">&#x27;女&#x27;</span> <span class="keyword">END</span> sex,</span><br><span class="line">        username</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>
<p>当然还可以使用大于小于等的判断符，</p>
<h1 id="连接和组合"><a href="#连接和组合" class="headerlink" title="连接和组合"></a>连接和组合</h1><h2 id="连接（JOIN）"><a href="#连接（JOIN）" class="headerlink" title="连接（JOIN）"></a>连接（JOIN）</h2><ul>
<li>如果一个 JOIN 至少有一个公共字段并且它们之间存在关系，则该 JOIN 可以在两个或多个表上工作。</li>
<li>连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。</li>
<li>JOIN 保持基表（结构和数据）不变。</li>
<li>JOIN 有两种连接类型：内连接和外连接。</li>
<li>内连接又称等值连接，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。<ul>
<li>自连接可以看成内连接的一种，只是连接的表是自身而已。</li>
</ul>
</li>
<li>自然连接是把同名列通过 &#x3D; 测试连接起来的，同名列可以有多个。</li>
<li>内连接 vs 自然连接<ul>
<li>内连接提供连接的列，而自然连接自动连接所有同名列。</li>
</ul>
</li>
<li>外连接返回一个表中的所有行，并且仅返回来自次表中满足连接条件的那些行，即两个表中的列是相等的。外连接分为左外连接、右外连接、全外连接（Mysql 不支持）。<ul>
<li>左外连接就是保留左表没有关联的行。</li>
<li>右外连接就是保留右表没有关联的行。</li>
</ul>
</li>
<li>连接 vs 子查询<ul>
<li>连接可以替换子查询，并且比子查询的效率一般会更快。</li>
</ul>
</li>
</ul>
<img data-src="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/3.webp" class="">

<p>内连接（INNER JOIN）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> vend_name, prod_name, prod_price</span><br><span class="line"><span class="keyword">FROM</span> vendors <span class="keyword">INNER</span> <span class="keyword">JOIN</span> products</span><br><span class="line"><span class="keyword">ON</span> vendors.vend_id <span class="operator">=</span> products.vend_id;</span><br></pre></td></tr></table></figure>
<p>自连接</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> c1.cust_id, c1.cust_name, c1.cust_contact</span><br><span class="line"><span class="keyword">FROM</span> customers c1, customers c2</span><br><span class="line"><span class="keyword">WHERE</span> c1.cust_name <span class="operator">=</span> c2.cust_name</span><br><span class="line"><span class="keyword">AND</span> c2.cust_contact <span class="operator">=</span> <span class="string">&#x27;Jim Jones&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p>自然连接（NATURAL JOIN）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> Products</span><br><span class="line"><span class="keyword">NATURAL</span> <span class="keyword">JOIN</span> Customers;</span><br></pre></td></tr></table></figure>
<p>左连接（LEFT JOIN）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> customers.cust_id, orders.order_num</span><br><span class="line"><span class="keyword">FROM</span> customers <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> orders</span><br><span class="line"><span class="keyword">ON</span> customers.cust_id <span class="operator">=</span> orders.cust_id;</span><br></pre></td></tr></table></figure>
<p>右连接（RIGHT JOIN）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> customers.cust_id, orders.order_num</span><br><span class="line"><span class="keyword">FROM</span> customers <span class="keyword">RIGHT</span> <span class="keyword">JOIN</span> orders</span><br><span class="line"><span class="keyword">ON</span> customers.cust_id <span class="operator">=</span> orders.cust_id;</span><br></pre></td></tr></table></figure>

<h2 id="组合（UNION）"><a href="#组合（UNION）" class="headerlink" title="组合（UNION）"></a>组合（UNION）</h2><ul>
<li>UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。</li>
<li>UNION 基本规则<ul>
<li>所有查询的列数和列顺序必须相同。</li>
<li>每个查询中涉及表的列的数据类型必须相同或兼容。</li>
<li>通常返回的列名取自第一个查询。</li>
</ul>
</li>
<li>默认会去除相同行，如果需要保留相同行，使用 UNION ALL。</li>
<li>只能包含一个 ORDER BY 子句，并且必须位于语句的最后。</li>
<li>应用场景<ul>
<li>在一个查询中从不同的表返回结构数据。</li>
<li>对一个表执行多个查询，按一个查询返回数据。</li>
</ul>
</li>
</ul>
<p>组合查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> cust_name, cust_contact, cust_email</span><br><span class="line"><span class="keyword">FROM</span> customers</span><br><span class="line"><span class="keyword">WHERE</span> cust_state <span class="keyword">IN</span> (<span class="string">&#x27;IL&#x27;</span>, <span class="string">&#x27;IN&#x27;</span>, <span class="string">&#x27;MI&#x27;</span>)</span><br><span class="line"><span class="keyword">UNION</span></span><br><span class="line"><span class="keyword">SELECT</span> cust_name, cust_contact, cust_email</span><br><span class="line"><span class="keyword">FROM</span> customers</span><br><span class="line"><span class="keyword">WHERE</span> cust_name <span class="operator">=</span> <span class="string">&#x27;Fun4All&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h2 id="JOIN-vs-UNION"><a href="#JOIN-vs-UNION" class="headerlink" title="JOIN vs UNION"></a>JOIN vs UNION</h2><ul>
<li>JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。</li>
<li>UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。</li>
</ul>
<h1 id="函数"><a href="#函数" class="headerlink" title="函数"></a>函数</h1><p>🔔 <strong>注意：不同数据库的函数往往各不相同，因此不可移植。本节主要以 Mysql 的函数为例。</strong></p>
<h2 id="文本处理"><a href="#文本处理" class="headerlink" title="文本处理"></a>文本处理</h2><img data-src="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/4.webp" class="">

<p>其中， <strong>SOUNDEX()</strong> 可以将一个字符串转换为描述其语音表示的字母数字模式。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> SOUNDEX(col1) <span class="operator">=</span> SOUNDEX(<span class="string">&#x27;apple&#x27;</span>)</span><br></pre></td></tr></table></figure>

<h2 id="日期和时间处理"><a href="#日期和时间处理" class="headerlink" title="日期和时间处理"></a>日期和时间处理</h2><ul>
<li>日期格式：YYYY-MM-DD</li>
<li>时间格式：HH:MM:SS<img data-src="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/5.webp" class="">
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">SELECT</span> NOW();</span><br><span class="line"></span><br><span class="line"><span class="number">2020</span><span class="number">-11</span><span class="number">-20</span> <span class="number">20</span>:<span class="number">20</span>:<span class="number">20</span></span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="数值处理"><a href="#数值处理" class="headerlink" title="数值处理"></a>数值处理</h2><img data-src="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/6.webp" class="">

<h2 id="汇总"><a href="#汇总" class="headerlink" title="汇总"></a>汇总</h2><img data-src="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/7.webp" class="">

<p>使用 DISTINCT 可以让汇总函数值汇总不同的值。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">AVG</span>(<span class="keyword">DISTINCT</span> col1) <span class="keyword">AS</span> avg_col</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br></pre></td></tr></table></figure>

<h1 id="排序和分组"><a href="#排序和分组" class="headerlink" title="排序和分组"></a>排序和分组</h1><h2 id="ORDER-BY"><a href="#ORDER-BY" class="headerlink" title="ORDER BY"></a>ORDER BY</h2><ul>
<li>ORDER BY 用于对结果集进行排序。<ul>
<li>ASC ：升序（默认）</li>
<li>DESC ：降序</li>
</ul>
</li>
<li>可以按多个列进行排序，并且为每个列指定不同的排序方式</li>
</ul>
<p>指定多个列的排序方向</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> products</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_price <span class="keyword">DESC</span>, prod_name <span class="keyword">ASC</span>;</span><br></pre></td></tr></table></figure>

<h2 id="GROUP-BY"><a href="#GROUP-BY" class="headerlink" title="GROUP BY"></a>GROUP BY</h2><ul>
<li>GROUP BY 子句将记录分组到汇总行中。</li>
<li>GROUP BY 为每个组返回一个记录。</li>
<li>GROUP BY 通常还涉及聚合：COUNT，MAX，SUM，AVG 等。</li>
<li>GROUP BY 可以按一列或多列进行分组。</li>
<li>GROUP BY 按分组字段进行排序后，ORDER BY 可以以汇总字段来进行排序。</li>
</ul>
<p>分组</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> cust_name, <span class="built_in">COUNT</span>(cust_address) <span class="keyword">AS</span> addr_num</span><br><span class="line"><span class="keyword">FROM</span> Customers <span class="keyword">GROUP</span> <span class="keyword">BY</span> cust_name;</span><br></pre></td></tr></table></figure>
<p>分组后排序</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> cust_name, <span class="built_in">COUNT</span>(cust_address) <span class="keyword">AS</span> addr_num</span><br><span class="line"><span class="keyword">FROM</span> Customers <span class="keyword">GROUP</span> <span class="keyword">BY</span> cust_name</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> cust_name <span class="keyword">DESC</span>;</span><br></pre></td></tr></table></figure>

<h2 id="HAVING"><a href="#HAVING" class="headerlink" title="HAVING"></a>HAVING</h2><ul>
<li>HAVING 用于对汇总的 GROUP BY 结果进行过滤。</li>
<li>HAVING 要求存在一个 GROUP BY 子句。</li>
<li>WHERE 和 HAVING 可以在相同的查询中。</li>
<li>HAVING vs WHERE<br>WHERE 和 HAVING 都是用于过滤。<br>HAVING 适用于汇总的组记录；而 WHERE 适用于单个记录。</li>
</ul>
<p>使用 WHERE 和 HAVING 过滤数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> cust_name, <span class="built_in">COUNT</span>(<span class="operator">*</span>) <span class="keyword">AS</span> num</span><br><span class="line"><span class="keyword">FROM</span> Customers</span><br><span class="line"><span class="keyword">WHERE</span> cust_email <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span></span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> cust_name</span><br><span class="line"><span class="keyword">HAVING</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>) <span class="operator">&gt;=</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h1 id="数据定义"><a href="#数据定义" class="headerlink" title="数据定义"></a>数据定义</h1><p>下面为DDL语句用法</p>
<blockquote>
<p>DDL 的主要功能是定义数据库对象（如：数据库、数据表、视图、索引等）。</p>
</blockquote>
<h2 id="数据库（DATABASE）"><a href="#数据库（DATABASE）" class="headerlink" title="数据库（DATABASE）"></a>数据库（DATABASE）</h2><p>创建数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> DATABASE test;</span><br></pre></td></tr></table></figure>

<p>删除数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> DATABASE test;</span><br></pre></td></tr></table></figure>

<p>选择数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">USE test;</span><br></pre></td></tr></table></figure>

<h2 id="数据表（TABLE）"><a href="#数据表（TABLE）" class="headerlink" title="数据表（TABLE）"></a>数据表（TABLE）</h2><p>创建数据表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> DATABASE test;</span><br></pre></td></tr></table></figure>
<p>删除数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> DATABASE test;</span><br></pre></td></tr></table></figure>
<p>选择数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">USE test;</span><br></pre></td></tr></table></figure>

<h2 id="数据表（TABLE）-1"><a href="#数据表（TABLE）-1" class="headerlink" title="数据表（TABLE）"></a>数据表（TABLE）</h2><p>普通的创建数据表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="keyword">user</span> (</span><br><span class="line">  id <span class="type">int</span>(<span class="number">10</span>) unsigned <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;Id&#x27;</span>,</span><br><span class="line">  username <span class="type">varchar</span>(<span class="number">64</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">&#x27;default&#x27;</span> COMMENT <span class="string">&#x27;用户名&#x27;</span>,</span><br><span class="line">  password <span class="type">varchar</span>(<span class="number">64</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">&#x27;default&#x27;</span> COMMENT <span class="string">&#x27;密码&#x27;</span>,</span><br><span class="line">  email <span class="type">varchar</span>(<span class="number">64</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">&#x27;default&#x27;</span> COMMENT <span class="string">&#x27;邮箱&#x27;</span></span><br><span class="line">) COMMENT<span class="operator">=</span><span class="string">&#x27;用户表&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p>根据已有的表创建新表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> vip_user <span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>

<p>删除数据表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>

<p>修改数据表:<br>添加列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">ADD</span> age <span class="type">int</span>(<span class="number">3</span>);</span><br></pre></td></tr></table></figure>
<p>删除列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">COLUMN</span> age;</span><br></pre></td></tr></table></figure>
<p>修改列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> `<span class="keyword">user</span>`</span><br><span class="line">MODIFY <span class="keyword">COLUMN</span> age tinyint;</span><br></pre></td></tr></table></figure>
<p>添加主键</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">PRIMARY</span> KEY (id);</span><br></pre></td></tr></table></figure>
<p>删除主键</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">PRIMARY</span> KEY;</span><br></pre></td></tr></table></figure>

<h2 id="视图（VIEW）"><a href="#视图（VIEW）" class="headerlink" title="视图（VIEW）"></a>视图（VIEW）</h2><ul>
<li>定义<ul>
<li>视图是基于 SQL 语句的结果集的可视化的表。</li>
<li>视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。</li>
</ul>
</li>
<li>作用<ul>
<li>简化复杂的 SQL 操作，比如复杂的联结；</li>
<li>只使用实际表的一部分数据；</li>
<li>通过只给用户访问视图的权限，保证数据的安全性；</li>
<li>更改数据格式和表示。</li>
</ul>
</li>
</ul>
<p>创建视图</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> top_10_user_view <span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> id, username</span><br><span class="line"><span class="keyword">FROM</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">WHERE</span> id <span class="operator">&lt;</span> <span class="number">10</span>;</span><br></pre></td></tr></table></figure>
<p>删除视图</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">VIEW</span> top_10_user_view;</span><br></pre></td></tr></table></figure>

<h2 id="索引（INDEX）"><a href="#索引（INDEX）" class="headerlink" title="索引（INDEX）"></a>索引（INDEX）</h2><ul>
<li>作用<ul>
<li>通过索引可以更加快速高效地查询数据。</li>
<li>用户无法看到索引，它们只能被用来加速查询。</li>
</ul>
</li>
<li>注意<ul>
<li>更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。</li>
</ul>
</li>
<li>唯一索引<ul>
<li>唯一索引表明此索引的每一个索引值只对应唯一的数据记录。</li>
</ul>
</li>
</ul>
<p>创建索引</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> INDEX user_index</span><br><span class="line"><span class="keyword">ON</span> <span class="keyword">user</span> (id);</span><br></pre></td></tr></table></figure>
<p>创建唯一索引</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">CREATE UNIQUE INDEX user_index</span><br><span class="line">ON user (id);</span><br></pre></td></tr></table></figure>
<p>删除索引</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="keyword">user</span></span><br><span class="line"><span class="keyword">DROP</span> INDEX user_index;</span><br></pre></td></tr></table></figure>

<h2 id="约束"><a href="#约束" class="headerlink" title="约束"></a>约束</h2><blockquote>
<p>SQL 约束用于规定表中的数据规则。</p>
</blockquote>
<ul>
<li>如果存在违反约束的数据行为，行为会被约束终止。</li>
<li>约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。</li>
<li>约束类型<ul>
<li>NOT NULL - 指示某列不能存储 NULL 值。</li>
<li>UNIQUE - 保证某列的每行必须有唯一的值。</li>
<li>PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。</li>
<li>FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。</li>
<li>CHECK - 保证列中的值符合指定的条件。</li>
<li>DEFAULT - 规定没有给列赋值时的默认值。</li>
</ul>
</li>
</ul>
<p>创建表时使用约束条件：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> Users (</span><br><span class="line">  Id <span class="type">INT</span>(<span class="number">10</span>) UNSIGNED <span class="keyword">NOT</span> <span class="keyword">NULL</span> AUTO_INCREMENT COMMENT <span class="string">&#x27;自增Id&#x27;</span>,</span><br><span class="line">  Username <span class="type">VARCHAR</span>(<span class="number">64</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">UNIQUE</span> <span class="keyword">DEFAULT</span> <span class="string">&#x27;default&#x27;</span> COMMENT <span class="string">&#x27;用户名&#x27;</span>,</span><br><span class="line">  Password <span class="type">VARCHAR</span>(<span class="number">64</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">&#x27;default&#x27;</span> COMMENT <span class="string">&#x27;密码&#x27;</span>,</span><br><span class="line">  Email <span class="type">VARCHAR</span>(<span class="number">64</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">&#x27;default&#x27;</span> COMMENT <span class="string">&#x27;邮箱地址&#x27;</span>,</span><br><span class="line">  Enabled TINYINT(<span class="number">4</span>) <span class="keyword">DEFAULT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;是否有效&#x27;</span>,</span><br><span class="line">  <span class="keyword">PRIMARY</span> KEY (Id)</span><br><span class="line">) ENGINE<span class="operator">=</span>InnoDB AUTO_INCREMENT<span class="operator">=</span><span class="number">2</span> <span class="keyword">DEFAULT</span> CHARSET<span class="operator">=</span>utf8mb4 COMMENT<span class="operator">=</span><span class="string">&#x27;用户表&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h1 id="事务处理"><a href="#事务处理" class="headerlink" title="事务处理"></a>事务处理</h1><p>下面属于DCL语句</p>
<ul>
<li>不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。</li>
<li>MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。</li>
<li>通过 set autocommit&#x3D;0 可以取消自动提交，直到 set autocommit&#x3D;1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。</li>
<li>指令<ul>
<li>START TRANSACTION - 指令用于标记事务的起始点。</li>
<li>SAVEPOINT - 指令用于创建保留点。</li>
<li>ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。</li>
<li>COMMIT - 提交事务。</li>
</ul>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 开始事务</span></span><br><span class="line"><span class="keyword">START</span> TRANSACTION;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入操作 A</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `<span class="keyword">user</span>`</span><br><span class="line"><span class="keyword">VALUES</span> (<span class="number">1</span>, <span class="string">&#x27;root1&#x27;</span>, <span class="string">&#x27;root1&#x27;</span>, <span class="string">&#x27;xxxx@163.com&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 创建保留点 updateA</span></span><br><span class="line"><span class="keyword">SAVEPOINT</span> updateA;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 插入操作 B</span></span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> `<span class="keyword">user</span>`</span><br><span class="line"><span class="keyword">VALUES</span> (<span class="number">2</span>, <span class="string">&#x27;root2&#x27;</span>, <span class="string">&#x27;root2&#x27;</span>, <span class="string">&#x27;xxxx@163.com&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 回滚到保留点 updateA</span></span><br><span class="line"><span class="keyword">ROLLBACK</span> <span class="keyword">TO</span> updateA;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 提交事务，只有操作 A 生效</span></span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br></pre></td></tr></table></figure>

<h1 id="权限控制"><a href="#权限控制" class="headerlink" title="权限控制"></a>权限控制</h1><ul>
<li>GRANT 和 REVOKE 可在几个层次上控制访问权限：<ul>
<li>整个服务器，使用 GRANT ALL 和 REVOKE ALL；</li>
<li>整个数据库，使用 ON database.*；</li>
<li>特定的表，使用 ON database.table；</li>
<li>特定的列；</li>
<li>特定的存储过程。</li>
</ul>
</li>
<li>新创建的账户没有任何权限。</li>
<li>账户用 username@host 的形式定义，username@% 使用的是默认主机名。</li>
<li>MySQL 的账户信息保存在 mysql 这个数据库中。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 可以这样查看</span></span><br><span class="line">USE mysql;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">user</span> <span class="keyword">FROM</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>

<p>创建账户</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">USER</span> myuser IDENTIFIED <span class="keyword">BY</span> <span class="string">&#x27;mypassword&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p>修改账户名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> <span class="keyword">user</span> <span class="keyword">SET</span> <span class="keyword">user</span><span class="operator">=</span><span class="string">&#x27;newuser&#x27;</span> <span class="keyword">WHERE</span> <span class="keyword">user</span><span class="operator">=</span><span class="string">&#x27;myuser&#x27;</span>;</span><br><span class="line">FLUSH PRIVILEGES;</span><br></pre></td></tr></table></figure>
<p>删除账户</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">USER</span> myuser;</span><br></pre></td></tr></table></figure>
<p>查看权限</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> GRANTS <span class="keyword">FOR</span> myuser;</span><br></pre></td></tr></table></figure>
<p>授予权限</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">GRANT</span> <span class="keyword">SELECT</span>, <span class="keyword">INSERT</span> <span class="keyword">ON</span> <span class="operator">*</span>.<span class="operator">*</span> <span class="keyword">TO</span> myuser;</span><br></pre></td></tr></table></figure>
<p>删除权限</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">REVOKE</span> <span class="keyword">SELECT</span>, <span class="keyword">INSERT</span> <span class="keyword">ON</span> <span class="operator">*</span>.<span class="operator">*</span> <span class="keyword">FROM</span> myuser;</span><br></pre></td></tr></table></figure>
<p>更改密码</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SET</span> PASSWORD <span class="keyword">FOR</span> myuser <span class="operator">=</span> <span class="string">&#x27;mypass&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h1 id="存储过程"><a href="#存储过程" class="headerlink" title="存储过程"></a>存储过程</h1><ul>
<li>存储过程可以看成是对一系列 SQL 操作的批处理；</li>
<li>使用存储过程的好处<ul>
<li>代码封装，保证了一定的安全性；</li>
<li>代码复用；</li>
<li>由于是预先编译，因此具有很高的性能。</li>
</ul>
</li>
<li>创建存储过程<ul>
<li>命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。</li>
<li>包含 in、out 和 inout 三种参数。</li>
<li>给变量赋值都需要用 select into 语句。</li>
<li>每次只能给一个变量赋值，不支持集合的操作。</li>
</ul>
</li>
</ul>
<p>创建存储过程</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">PROCEDURE</span> IF <span class="keyword">EXISTS</span> `proc_adder`;</span><br><span class="line">DELIMITER ;;</span><br><span class="line"><span class="keyword">CREATE</span> DEFINER<span class="operator">=</span>`root`@`localhost` <span class="keyword">PROCEDURE</span> `proc_adder`(<span class="keyword">IN</span> a <span class="type">int</span>, <span class="keyword">IN</span> b <span class="type">int</span>, <span class="keyword">OUT</span> sum <span class="type">int</span>)</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">    <span class="keyword">DECLARE</span> c <span class="type">int</span>;</span><br><span class="line">    if a <span class="keyword">is</span> <span class="keyword">null</span> <span class="keyword">then</span> <span class="keyword">set</span> a <span class="operator">=</span> <span class="number">0</span>;</span><br><span class="line">    <span class="keyword">end</span> if;</span><br><span class="line"></span><br><span class="line">    if b <span class="keyword">is</span> <span class="keyword">null</span> <span class="keyword">then</span> <span class="keyword">set</span> b <span class="operator">=</span> <span class="number">0</span>;</span><br><span class="line">    <span class="keyword">end</span> if;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">set</span> sum  <span class="operator">=</span> a <span class="operator">+</span> b;</span><br><span class="line"><span class="keyword">END</span></span><br><span class="line">;;</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>

<p>使用存储过程</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">set</span> <span class="variable">@b</span><span class="operator">=</span><span class="number">5</span>;</span><br><span class="line"><span class="keyword">call</span> proc_adder(<span class="number">2</span>,<span class="variable">@b</span>,<span class="variable">@s</span>);</span><br><span class="line"><span class="keyword">select</span> <span class="variable">@s</span> <span class="keyword">as</span> sum;</span><br></pre></td></tr></table></figure>

<h1 id="游标"><a href="#游标" class="headerlink" title="游标"></a>游标</h1><ul>
<li>游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。</li>
<li>在存储过程中使用游标可以对一个结果集进行移动遍历。</li>
<li>游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。</li>
<li>使用游标的四个步骤：<ul>
<li>声明游标，这个过程没有实际检索出数据；</li>
<li>打开游标；</li>
<li>取出数据；</li>
<li>关闭游标；</li>
</ul>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER $</span><br><span class="line"><span class="keyword">CREATE</span>  <span class="keyword">PROCEDURE</span> getTotal()</span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">    <span class="keyword">DECLARE</span> total <span class="type">INT</span>;</span><br><span class="line">    <span class="comment">-- 创建接收游标数据的变量</span></span><br><span class="line">    <span class="keyword">DECLARE</span> sid <span class="type">INT</span>;</span><br><span class="line">    <span class="keyword">DECLARE</span> sname <span class="type">VARCHAR</span>(<span class="number">10</span>);</span><br><span class="line">    <span class="comment">-- 创建总数变量</span></span><br><span class="line">    <span class="keyword">DECLARE</span> sage <span class="type">INT</span>;</span><br><span class="line">    <span class="comment">-- 创建结束标志变量</span></span><br><span class="line">    <span class="keyword">DECLARE</span> done <span class="type">INT</span> <span class="keyword">DEFAULT</span> <span class="literal">false</span>;</span><br><span class="line">    <span class="comment">-- 创建游标</span></span><br><span class="line">    <span class="keyword">DECLARE</span> cur <span class="keyword">CURSOR</span> <span class="keyword">FOR</span> <span class="keyword">SELECT</span> id,name,age <span class="keyword">from</span> cursor_table <span class="keyword">where</span> age<span class="operator">&gt;</span><span class="number">30</span>;</span><br><span class="line">    <span class="comment">-- 指定游标循环结束时的返回值</span></span><br><span class="line">    <span class="keyword">DECLARE</span> CONTINUE HANDLER <span class="keyword">FOR</span> <span class="keyword">NOT</span> FOUND <span class="keyword">SET</span> done <span class="operator">=</span> <span class="literal">true</span>;</span><br><span class="line">    <span class="keyword">SET</span> total <span class="operator">=</span> <span class="number">0</span>;</span><br><span class="line">    <span class="keyword">OPEN</span> cur;</span><br><span class="line">    <span class="keyword">FETCH</span> cur <span class="keyword">INTO</span> sid, sname, sage;</span><br><span class="line">    WHILE(<span class="keyword">NOT</span> done)</span><br><span class="line">    DO</span><br><span class="line">        <span class="keyword">SET</span> total <span class="operator">=</span> total <span class="operator">+</span> <span class="number">1</span>;</span><br><span class="line">        <span class="keyword">FETCH</span> cur <span class="keyword">INTO</span> sid, sname, sage;</span><br><span class="line">    <span class="keyword">END</span> WHILE;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">CLOSE</span> cur;</span><br><span class="line">    <span class="keyword">SELECT</span> total;</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line">DELIMITER ;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 调用存储过程</span></span><br><span class="line"><span class="keyword">call</span> getTotal();</span><br></pre></td></tr></table></figure>

<h1 id="触发器"><a href="#触发器" class="headerlink" title="触发器"></a>触发器</h1><blockquote>
<p>触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。</p>
</blockquote>
<p>可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。MySQL 不允许在触发器中使用 CALL 语句 ，也就是不能调用存储过程。</p>
<h2 id="BEGIN-和-END"><a href="#BEGIN-和-END" class="headerlink" title="BEGIN 和 END"></a>BEGIN 和 END</h2><p>当触发器的触发条件满足时，将会执行 BEGIN 和 END 之间的触发器执行动作。</p>
<blockquote>
<p>🔔 注意：在 MySQL 中，分号 ; 是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL 可以开始执行了。因此，解释器遇到触发器执行动作中的分号后就开始执行，然后会报错，因为没有找到和 BEGIN 匹配的 END。<br> 这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思）。它是一条命令，不需要语句结束标识，语法为：DELIMITER new_delemiter。new_delemiter 可以设为 1 个或多个长度的符号，默认的是分号 ;，我们可以把它修改为其他符号，如 $ - DELIMITER $ 。在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了 $，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。</p>
</blockquote>
<h2 id="NEW-和-OLD"><a href="#NEW-和-OLD" class="headerlink" title="NEW 和 OLD"></a>NEW 和 OLD</h2><ul>
<li>MySQL 中定义了 NEW 和 OLD 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。</li>
<li>在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；</li>
<li>在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；</li>
<li>在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；</li>
<li>使用方法： NEW.columnName （columnName 为相应数据表某一列名）</li>
</ul>
<h2 id="创建触发器"><a href="#创建触发器" class="headerlink" title="创建触发器"></a>创建触发器</h2><blockquote>
<p>提示：为了理解触发器的要点，有必要先了解一下创建触发器的指令。</p>
</blockquote>
<p>CREATE TRIGGER 指令用于创建触发器。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> trigger_name</span><br><span class="line">trigger_time</span><br><span class="line">trigger_event</span><br><span class="line"><span class="keyword">ON</span> table_name</span><br><span class="line"><span class="keyword">FOR</span> <span class="keyword">EACH</span> <span class="type">ROW</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">  trigger_statements</span><br><span class="line"><span class="keyword">END</span>;</span><br></pre></td></tr></table></figure>
<p>说明：</p>
<ul>
<li>trigger_name：触发器名</li>
<li>trigger_time: 触发器的触发时机。取值为 BEFORE 或 AFTER。</li>
<li>trigger_event: 触发器的监听事件。取值为 INSERT、UPDATE 或 DELETE。</li>
<li>table_name: 触发器的监听目标。指定在哪张表上建立触发器。</li>
<li>FOR EACH ROW: 行级监视，Mysql 固定写法，其他 DBMS 不同。</li>
<li>trigger_statements: 触发器执行动作。是一条或多条 SQL 语句的列表，列表内的每条语句都必须用分号 ; 来结尾。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">DELIMITER $</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> `trigger_insert_user`</span><br><span class="line">AFTER <span class="keyword">INSERT</span> <span class="keyword">ON</span> `<span class="keyword">user</span>`</span><br><span class="line"><span class="keyword">FOR</span> <span class="keyword">EACH</span> <span class="type">ROW</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">    <span class="keyword">INSERT</span> <span class="keyword">INTO</span> `user_history`(user_id, operate_type, operate_time)</span><br><span class="line">    <span class="keyword">VALUES</span> (NEW.id, <span class="string">&#x27;add a user&#x27;</span>,  now());</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>

<h2 id="查看触发器"><a href="#查看触发器" class="headerlink" title="查看触发器"></a>查看触发器</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> TRIGGERS;</span><br></pre></td></tr></table></figure>

<h2 id="删除触发器"><a href="#删除触发器" class="headerlink" title="删除触发器"></a>删除触发器</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TRIGGER</span> IF <span class="keyword">EXISTS</span> trigger_insert_user;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>好了这些基本上就是SQL语言的基本知识了，掌握之后就能应对简单的CRUD业务开发了。但是MYSQL还有很多知识点，比如索引的类别、锁、查询优化器、读写分离、主备一致、数据库引擎等等需要你去学习哦！</p>
</blockquote>

    </div>

    
    
    

    <footer class="post-footer">
          

<div class="post-copyright">
<ul>
  <li class="post-copyright-author">
      <strong>本文作者： </strong>Wyatex
  </li>
  <li class="post-copyright-link">
      <strong>本文链接：</strong>
      <a href="https://wyatex.online/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/SQL-%E8%AF%AD%E6%B3%95%E9%80%9F%E6%88%90%E6%89%8B%E5%86%8C/" title="SQL 语法速成手册">https://wyatex.online/学习笔记/SQL-语法速成手册/</a>
  </li>
  <li class="post-copyright-license">
      <strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" rel="noopener" target="_blank"><i class="fab fa-fw fa-creative-commons"></i>BY-NC-SA</a> 许可协议。转载请注明出处！
  </li>
</ul>
</div>

          <div class="post-tags">
              <a href="/tags/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/" rel="tag"># 学习笔记</a>
              <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag"># 数据库</a>
              <a href="/tags/SQL/" rel="tag"># SQL</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86%E6%80%BB%E7%BB%93/" rel="prev" title="计算机网络基础知识总结">
                  <i class="fa fa-angle-left"></i> 计算机网络基础知识总结
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/%E5%BE%AE%E6%9C%BA%E4%B8%8E%E6%8E%A5%E5%8F%A3%E6%8A%80%E6%9C%AF%E7%AC%94%E8%AE%B0%EF%BC%884%EF%BC%89/" rel="next" title="微机与接口技术笔记（4）">
                  微机与接口技术笔记（4） <i class="fa fa-angle-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">

  <div class="copyright">
    &copy; 2019 – 
    <span itemprop="copyrightYear">2024</span>
    <span class="with-love">
      <i class="fa fa-heart"></i>
    </span>
    <span class="author" itemprop="copyrightHolder">Wyatex</span>
  </div>
<div class="busuanzi-count">
    <span class="post-meta-item" id="busuanzi_container_site_uv">
      <span class="post-meta-item-icon">
        <i class="fa fa-user"></i>
      </span>
      <span class="site-uv" title="总访客量">
        <span id="busuanzi_value_site_uv"></span>
      </span>
    </span>
    <span class="post-meta-item" id="busuanzi_container_site_pv">
      <span class="post-meta-item-icon">
        <i class="fa fa-eye"></i>
      </span>
      <span class="site-pv" title="总访问量">
        <span id="busuanzi_value_site_pv"></span>
      </span>
    </span>
</div>

    </div>
  </footer>

  
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up fa-lg"></i>
    <span>0%</span>
  </div>
  <div class="reading-progress-bar"></div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


  
  <script size="300" alpha="0.6" zIndex="-1" src="/lib/ribbon.js/dist/ribbon.min.js"></script>
  <script src="/lib/animejs/lib/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
  <script src="/lib/@next-theme/pjax/pjax.min.js" integrity="sha256-vxLn1tSKWD4dqbMRyv940UYw4sXgMtYcK6reefzZrao=" crossorigin="anonymous"></script>
  <script src="/lib/@fancyapps/ui/dist/fancybox/fancybox.umd.js" integrity="sha256-q8XkJ6dj5VwSvzI8+nATCHHQG+Xv/dAZBCgqmu93zOY=" crossorigin="anonymous"></script>
  <script src="/lib/lozad/dist/lozad.min.js" integrity="sha256-mOFREFhqmHeQbXpK2lp4nA3qooVgACfh88fpJftLBbc=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/next-boot.js"></script><script src="/js/pjax.js"></script>

  <script src="/lib/hexo-generator-searchdb/dist/search.js" integrity="sha256-1kfA5uHPf65M5cphT2dvymhkuyHPQp5A53EGZOnOLmc=" crossorigin="anonymous"></script>
<script src="/js/third-party/search/local-search.js"></script>




  <script src="/js/third-party/fancybox.js"></script>



  
  <script data-pjax async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>





</body>
</html>
